#Copyright (c) 2025, Alibaba Cloud and its affiliates;
#Licensed under the Apache License, Version 2.0 (the "License");
#you may not use this file except in compliance with the License.
#You may obtain a copy of the License at

#   http://www.apache.org/licenses/LICENSE-2.0

#Unless required by applicable law or agreed to in writing, software
#distributed under the License is distributed on an "AS IS" BASIS,
#WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#See the License for the specific language governing permissions and
#limitations under the License.



"""
DB libs
"""
import os

import common_libs.log as log
import common_libs.localrun as localrun
import mysql.connector.pooling as pooling

logging = log.Logger

class Singleton(object):

    def __new__(cls, *args, **kw):
        if not hasattr(cls, '_instance'):
            orig = super(Singleton, cls)
            cls._instance = orig.__new__(cls)
        return cls._instance


class mysqlClient(Singleton):
    def __init__(self, host='localhost', port='3306', user='root',
                password='', database='',
                pool_name='mysql', pool_size=5,):
        """Create a singleton object which is a connection pool.

        Args:
            host (str): host of mysql, default is localhost.
            port (int): port of mysql, default is 3306.
            user (int): user of mysql, default is root.
            password (str): password of mysql, default is None.
            database (str): database of connection pool.
            pool_name (str): name of connection pool, default is mysql.
            pool_size (int): size of connection pool, the connections no more 
              than the size, default is 5.
        """
        # Set connectionPool parameters
        self._host = host
        self._port = port
        self._user = user
        self._password = password
        self._database = database
        self._pool_name = pool_name
        self._pool_size = pool_size
        self._dbConfig = {
          "host": self._host,
          "port": self._port,
          "user": self._user,
          "password": self._password,
          "database": self._database,
        }
        
        
        try:
          self.pool = self.createPool(self._pool_name, self._pool_size)
        except Exception as e:
          logging.error('failed to create connecting pool, error details: %s. Will try again' % e)
          (status, _) = localrun.run(
            "mysql -u root -e \"SET PASSWORD FOR 'root'@'localhost'= PASSWORD('');\""
          )
          if status != 0:
            logging.error('failed to reset password for root, exit.')
            raise e
          (status, _) = localrun.run("systemctl restart mysql")
          if status != 0:
            logging.error('failed to restart mysql, exit.')
            raise e
          self.pool = self.createPool(self._pool_name, self._pool_size)
    
    
    def createPool(self, pool_name='mysql', pool_size=5):
      """Create a connection pool, and all requestes of connecting to mysql
        should use get_connection from the pool.

      Args:
          pool_name (str): name of connection pool, default is mysql.
          pool_size (int): size of connection pool, default is 5.
      """
      return pooling.MySQLConnectionPool(pool_name=pool_name, pool_size=pool_size, 
                            pool_reset_session=True, **self._dbConfig)
    
    
    def getConnect(self, with_column_name = False):
      """Get a connection from connection poll. If the pool is full, will
        throw PoolError for the connecting request.

      Args:
          None
      """
      try:
        conn = self.pool.get_connection()
        cursor = conn.cursor(dictionary=with_column_name)
        return conn, cursor
      except Exception as e:
        logging.error("Current pool is full of connections. %s" % e)

    def closeConnect(self, conn, cursor):
      """Release a pooled connection obtained from a connection pool.
      Doesn't actually close the connection but returns it to the pool 
      and makes it available for subsequent connection requests.

      Args:
          conn (Connection): to be closed connection.
          cursor (Cursor): cursor of the connection.
      """
      cursor.close()
      conn.close()

    
    def execute(self, sql, value=None, commit=False, with_column=False):
        """Execute command on the database. Default support query, if need insert,
          update and delete, should set commit to True.

        Args:
            sql (str): to be executed command.
            value (tuple/dictionary, optional): values of parameters in sql. Defaults to None.
            commit (bool, optional): Send a COMMIT statement to MySQL server. Defaults to False.

        Raises:
            e: PoolError. If connections in the pool is all used.

        Returns:
            list: query results, length is equal or bigger than 0.
        """
        conn, cursor = self.getConnect(with_column)
        try:
            cursor.execute(sql, value)
            if commit:
              conn.commit()
              self.closeConnect(conn, cursor)
              return None
            # not commit
            res = cursor.fetchall()
            self.closeConnect(conn, cursor)
            return res
        except Exception as e:
            logging.error('failed to execute sql: %s, error details: %s' % (sql, e))
            self.closeConnect(conn, cursor)
            raise e
    
    
    def get(self, sql, value=None, with_column=False):
        """Get record from database by using sql.

        Args:
            sql (str): to be executed command.
            value (tuple/dictionary, optional): values of parameters in sql. Defaults to None.
            with_column (bool, optional): whether result with column name. Defaults to False.

        Returns:
            dictionary: query results.
        """
        res = self.execute(sql, value, with_column=with_column)
        return res[0] if len(res) else {}

    def select(self, sql, value=None, with_column=False):
        """Get record from database by using sql.

        Args:
            sql (str): to be executed command.
            value (tuple/dictionary, optional): values of parameters in sql. Defaults to None.
            with_column (bool, optional): whether result with column name. Defaults to False.

        Returns:
            list: query results.
        """
        res = self.execute(sql, value, with_column=with_column)
        return res
    
    def operate(self, sql, value=None):
        """operate database by using sql.

        Args:
            sql (str): to be executed command.
            value (tuple/dictionary, optional): values of parameters in sql. Defaults to None.

        Returns:
            list: query results.
        """
        res = self.execute(sql, value, True)
        return res

    def count(self, sql, value=None):
        """Get record count from database by using sql.

        Args:
            sql (str): to be executed command.
            value (tuple/dictionary, optional): values of parameters in sql. Defaults to None.

        Returns:
            num: record count.
        """
        res = self.execute(sql, value)
        if res == None:
            return -1
        else:
            return len(res)
